using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Collections.Generic;
using webapp4U.BOL;

namespace webapp4U.DAL
{
    public class BoardDAL : BaseDAL
    {

        public string GetImageURLByBoardID(int BoardID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                try
                {
                    string value = "";
                    SqlCommand myCommand = new SqlCommand("Board_GetImageURLByBoardID", myConn);
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.Parameters.Add("@ImageURL_Board", SqlDbType.Int).Value = BoardID;
                    value = (string)myCommand.ExecuteScalar();
                    myConn.Close();
                    return value;
                }
                catch { return ""; }
            }

        }
        

        //Get All Article
        public List<Board> GetAllBoard(int PageIndex, int PageSize)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                SqlCommand myCommand = new SqlCommand("Board_GetAllBoard", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndex;
                myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize;
                SqlDataReader dr = myCommand.ExecuteReader();
                return GetBoardCollectionFromReader(dr);
            }
        }

        // Get Count All records of Table  Article
        public int GetCountBoard()
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                SqlCommand myCommand = new SqlCommand("Board_GetCountBoard", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                return (int)myCommand.ExecuteScalar();
            }
        }

       

        public Board GetBoardByBoardID(int BoardID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                Board obj = new Board();
                SqlCommand myCommand = new SqlCommand("Board_getBoardByBoardID", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@Board_ID", SqlDbType.Int).Value = BoardID;
                SqlDataReader dr = myCommand.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        obj = BoardIDataReader(dr);
                    }
                    dr.Close();
                    myConn.Close();
                }
                return obj;
            }     
        }


       

        private  Board BoardIDataReader(IDataReader dr)
        {
            Board obj = new Board();
            obj.BoardID = (dr["ID_Board"] is DBNull) ? int.MinValue : (int)dr["ID_Board"];
            obj.ImageURL = (dr["ImageURL_Board"] is DBNull) ? string.Empty : (string)dr["ImageURL_Board"];
            obj.Information = (dr["Information_Board"] is DBNull) ? string.Empty : (string)dr["Information_Board"];            
            return obj;
        }
        
        /// <summary>
        /// Returns a collection of Article with the data read from the input DataReader
        /// </summary>
        private List<Board> GetBoardCollectionFromReader(IDataReader dr)
        {
            List<Board> obj = new List<Board>();
            while (dr.Read())
                obj.Add(BoardIDataReader(dr));
            return obj;
        }

        
        public int Insert(Board obj)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                SqlCommand myCommand = new SqlCommand("Board_Insert", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@ImageURL_Board", SqlDbType.NVarChar).Value = obj.ImageURL;
                myCommand.Parameters.Add("@Information_Board", SqlDbType.NText).Value = obj.Information;
                myCommand.ExecuteNonQuery();
                myConn.Close();
                return 1;
            }
        }

        public int Update(Board obj)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                SqlCommand myCommand = new SqlCommand("Board_Update", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@Board_ID", SqlDbType.Int).Value = obj.BoardID;
                myCommand.Parameters.Add("@ImageURL_Board", SqlDbType.NVarChar).Value = obj.ImageURL;
                myCommand.Parameters.Add("@Information_Board", SqlDbType.NText).Value = obj.Information;
                myCommand.ExecuteNonQuery();
                myConn.Close();
                return 1;
            }
        }

        public int Delete(int BoardID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                SqlCommand myCommand = new SqlCommand("Board_Delete", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@Board_ID", SqlDbType.Int).Value = BoardID;
                myCommand.ExecuteNonQuery();
                myConn.Close();
                return 1;
            }
        }


	}

}
